﻿
CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN COALESCE(ValPlatCuTVA,0)> ValoareTotalaSiTVA THEN ValoareTotalaSiTVA ELSE COALESCE(ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE ValPlatCuTVA END ValPlatCuTVA,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
	  CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza 
	END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
      CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END
    ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) 
	THEN Baza - CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza 
	ELSE COALESCE(BazaExigibila,0) END  ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA - 
	 CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal
	LEFT OUTER JOIN
	(SELECT SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	
	   
	WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
	doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
	
	
 UNION
    --toate cele vechi neplatite de 90 de zile
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN Baza - CASE WHEN 
	ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END  - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA -
	CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	 - COALESCE(selPlatianterior.TVAExigibil,0)ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  (@cumparari=1 OR tipd.IDTipDocument in (1)) AND
	  (@cumparari=0 OR tipd.IDTipDocument in (0)) AND  -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (@cumparari=1 OR (((YEAR(DATEADD(day,90,doc.DataDoc))<=2013) AND (DATEADD(day,90,doc.DataDoc) > @PanaLaData)) OR (YEAR(DATEADD(day,90,doc.DataDoc))>=2014))) AND    -- dar nu mai vechi de 90 de zile
	  (@cumparari=0 OR doc.IDRegimFiscal IN (4,17,18,19,27)) AND         -- cu regim fiscal cu TVA interne
	  (@cumparari=1 OR doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	

 UNION
    --jurnal vanzari care devin exigibile in perioada curenta
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	Baza - COALESCE(selPlatiAnterior.BazaExigibila,0) BazaExigibila,  
	ValoareTVA - COALESCE(selPlatianterior.TVAExigibil,0)	TVAExigibil, 
	0 BazaNeexigibila,  
	0 TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  @cumparari=0 AND								--doar pentru jurnalul de vanzari
	  YEAR(doc.DataDoc) in (2012,2013) AND                     -- dupa 2012 
	  (tipd.IDTipDocument in (1)) AND
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData) AND    -- care devin exigibile le 90 de zile
	   YEAR(DATEADD(day,90,doc.DataDoc)) <=2013 AND
	  (doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	


	
   ORDER BY DataDoc
END